from dbutils.persistent_db import PersistentDB
import pymysql
import v2.MySqlConnector_Config as config
import datetime


class MySqlConnector(object):
    pool = None

    def __init__(self):
        c = self.connect()
        self.connector = c
        self.cursor = c.cursor()

    def connect(self):
        if self.pool is None:
            self.pool = PersistentDB(creator=pymysql,
                                     host=config.DB_HOST,
                                     port=config.DB_PORT,
                                     user=config.DB_USER,
                                     passwd=config.DB_PASSWD)
        return self.pool.connection()

    def close(self):
        self.cursor.close()
        self.connector.close()

    def __enter__(self):
        c = self.connect()
        self.connector = c
        self.cursor = c.cursor()

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.close()


class MySqlHelper(object):
    def __init__(self):
        self.db = MySqlConnector()

    def filter(self, **kwargs):
        if 'param' in kwargs:
            for element in kwargs['param']:
                if isinstance(element, datetime.datetime):
                    element = str(element)
            return kwargs['param']
        elif 'param_list' in kwargs:
            for el_tuple in kwargs['param_list']:
                for el in el_tuple:
                    if isinstance(el, datetime.datetime):
                        el = str(el)
            return kwargs['param_list']
        return None

    def insert(self, sql, param=()):
        param = self.filter(param=param)
        self.execute(sql, param)

    def select(self, sql, param=()):
        connect = self.db.connect()
        cursor = connect.cursor()
        try:
            if param:
                cursor.execute(sql, param)
            else:
                cursor.execute(sql)
            return cursor.fetchall()
        except Exception as e:
            print(e)
            connect.rollback()
        finally:
            cursor.close()

    def delete(self, sql, param=()):
        param = self.filter(param=param)
        self.execute(sql, param)

    def update(self, sql, param=()):
        param = self.filter(param=param)
        self.execute(sql, param)

    def insert_many(self, sql, param_list):
        param_list = self.filter(param_list=param_list)
        self.execute_many(sql=sql, param_list=param_list)

    def update_many(self, sql, param_list):
        param_list = self.filter(param_list=param_list)
        self.execute_many(sql, param_list)

    def execute(self, sql, param=()):
        """
        执行一条语句的时候用这个函数
        :param sql:
        :param param:
        :return:
        """
        connect = self.db.connect()
        cursor = connect.cursor()
        try:
            if param:
                cursor.execute(sql, param)
            else:
                cursor.execute()
            connect.commit()
        except Exception as e:
            connect.rollback()
            raise e
        finally:
            self.close(connect, cursor)
            pass

    def execute_many(self, sql, param_list=None):
        """
        一次执行多条sql语句时用这个函数
        例: execute_many(sql="insert into users('username', 'password') values(%s, %s)", param_list=[('gaowei', '123456')]
        :param sql: sql语句
        :param param_list: 参数列表
        :return: None
        """
        connect = self.db.connect()
        cursor = connect.cursor()

        try:
            if param_list:
                cursor.executemany(sql, param_list)
            else:
                cursor.executemany(sql)
            connect.commit()
        except Exception as e:
            connect.rollback()
            raise e
        finally:
            self.close(connect, cursor)

    def close(self, connect, cursor):
        cursor.close()
        connect.close()

